PRINT N'Creating stored procedures';
GO

CREATE PROCEDURE CreateCodeflowInstance 
	@codeflowType NVARCHAR(1024),
	@friendlyName NVARCHAR(1024),
	@status INT,
	@timeToWakeUp DATETIME = NULL,
	@lockedUntilUtc DATETIME,
	@dispatcherId UNIQUEIDENTIFIER,
	@createdOnUtc DATETIME,
	@modifiedOnUtc DATETIME,	
	@actionName NVARCHAR(MAX),
	@dispatchAttemptsCount INT,
	@argumentsJson NVARCHAR(MAX),
	@startedAtUtc DATETIME,
	@serializedArguments VARBINARY(MAX)
AS
	DECLARE @insertedAction TABLE(
		Id UNIQUEIDENTIFIER);
	DECLARE @insertedCodeflow TABLE(
		Id UNIQUEIDENTIFIER);
	DECLARE @actionId UNIQUEIDENTIFIER;
	DECLARE @codeflowId UNIQUEIDENTIFIER;
	DECLARE @DialogHandle UNIQUEIDENTIFIER;
	BEGIN
	BEGIN TRANSACTION
		-- Inert the CodeflowInstance
		INSERT INTO [dbo].[CodeflowInstances] (
			[CodeflowType], [FriendlyName], [Status], [TimeToWakeUp], [LockedUntilUtc],
			[DispatcherId], [ActionName], [DispatchAttemptsCount], [ArgumentsJson], [StartedAtUtc], [SerializedArguments], 
			[CreatedOnUtc], [ModifiedOnUtc])
		OUTPUT inserted.Id INTO @insertedCodeflow
		VALUES (@codeflowType, @friendlyName, @status,
			   @timeToWakeUp, @lockedUntilUtc, @dispatcherId, 
			   @actionName, @dispatchAttemptsCount, @argumentsJson, @startedAtUtc, @serializedArguments, 
			   @createdOnUtc, @modifiedOnUtc);

		SELECT TOP 1 @codeflowId = Id FROM @insertedCodeflow;
	
		-- Enqueue the ready message    
		BEGIN DIALOG @DialogHandle
			 FROM SERVICE
			  [http://schemas.codeflows.net/services/client]
			 TO SERVICE
			  N'http://schemas.codeflows.net/services/workitems'
			 ON CONTRACT
			  [http://schemas.codeflows.net/contracts/workitems]
			 WITH
				 ENCRYPTION = OFF;

		SEND ON CONVERSATION @DialogHandle
			 MESSAGE TYPE 
			 [http://schemas.codeflows.net/messages/workitems/enqueue]
			 (@codeflowId);
	     
		END CONVERSATION @DialogHandle
  
		SELECT @codeflowId;  
	COMMIT TRANSACTION
	END
GO

CREATE PROCEDURE WorkItemsReader
AS
BEGIN		
	DECLARE @dispatchResult INT
	DECLARE @handle UNIQUEIDENTIFIER
	DECLARE @codeflowId UNIQUEIDENTIFIER
	DECLARE @failureCount INT
	DECLARE @messages TABLE(
		 handle UNIQUEIDENTIFIER,     
		 codeflowId UNIQUEIDENTIFIER)   
	
	WHILE 1 = 1
	BEGIN
		BEGIN TRAN
			WAITFOR
			(RECEIVE TOP(1) conversation_handle, message_body
			FROM WorkItems
			INTO @messages), 
			TIMEOUT 0;				
			IF @@ROWCOUNT = 0 
			BEGIN
				COMMIT TRAN
				GOTO Finish
			END	
			
			SELECT @codeflowId = codeflowId, @handle = handle FROM @messages
			IF(@handle IS NOT NULL)
			BEGIN		
				END CONVERSATION @handle;    						
			END
			IF(@codeflowId IS NOT NULL)
			BEGIN
				INSERT INTO [CodeflowsDispatch] (Id) VALUES (@codeflowId)
			END		
		COMMIT TRAN		
		
		IF(@codeflowId IS NOT NULL)
		BEGIN
			EXEC DispatchCodeflow @codeflowId, @dispatchResult OUTPUT
			IF(@dispatchResult = 0) BEGIN
				BEGIN TRAN
					-- Enqueue retry message    
					DECLARE @retryDialogHandle UNIQUEIDENTIFIER								
					
					BEGIN DIALOG @retryDialogHandle
						 FROM SERVICE
						  [http://schemas.codeflows.net/services/client]
						 TO SERVICE
						  N'http://schemas.codeflows.net/services/faileditems'
						 ON CONTRACT
						  [http://schemas.codeflows.net/contracts/faileditems]
						 WITH
							 ENCRYPTION = OFF;

					SEND ON CONVERSATION @retryDialogHandle
						 MESSAGE TYPE 
						 [http://schemas.codeflows.net/messages/faileditems/retry]
						 (@codeflowId);	     
					END CONVERSATION @retryDialogHandle						
				COMMIT TRAN
			END
		END
	END	
	Finish:
END
GO


CREATE PROCEDURE FailedItemsReader
AS
BEGIN		
	DECLARE @dispatchResult INT
	DECLARE @handle UNIQUEIDENTIFIER
	DECLARE @codeflowId UNIQUEIDENTIFIER	
	DECLARE @messages TABLE(
		 handle UNIQUEIDENTIFIER,     		 
		 codeflowId UNIQUEIDENTIFIER)   
	
	WHILE 1 = 1
	BEGIN
		BEGIN TRAN
			WAITFOR
			(RECEIVE TOP(1) conversation_handle, message_body
			FROM FailedItems
			INTO @messages), 
			TIMEOUT 0;				
			IF @@ROWCOUNT = 0 
			BEGIN
				COMMIT TRAN
				GOTO Finish
			END	
			
			SELECT @codeflowId = codeflowId, @handle = handle FROM @messages
			IF(@handle IS NOT NULL)
			BEGIN		
				END CONVERSATION @handle;    						
			END
			IF(@codeflowId IS NOT NULL)
			BEGIN
				UPDATE [CodeflowsDispatch] SET LastDispatch = GETDATE() WHERE Id = @codeflowId
			END		
		COMMIT TRAN		
		
		IF(@codeflowId IS NOT NULL)
		BEGIN
			EXEC DispatchCodeflow @codeflowId, @dispatchResult OUTPUT
			IF(@dispatchResult = 0) BEGIN
				BEGIN TRAN
					-- Enqueue retry message    
					DECLARE @retryDialogHandle UNIQUEIDENTIFIER								
					
					BEGIN DIALOG @retryDialogHandle
						 FROM SERVICE
						  [http://schemas.codeflows.net/services/client]
						 TO SERVICE
						  N'http://schemas.codeflows.net/services/faileditems'
						 ON CONTRACT
						  [http://schemas.codeflows.net/contracts/faileditems]
						 WITH
							 ENCRYPTION = OFF;

					SEND ON CONVERSATION @retryDialogHandle
						 MESSAGE TYPE 
						 [http://schemas.codeflows.net/messages/faileditems/retry]
						 (@codeflowId);	     
					END CONVERSATION @retryDialogHandle						
				COMMIT TRAN
				GOTO Finish
			END
		END
	END	
	Finish:
END
GO

CREATE PROCEDURE ControlQueueReader
AS
BEGIN		
	DECLARE @dispatchResult INT
	DECLARE @handle UNIQUEIDENTIFIER
	DECLARE @messageType NVARCHAR(256)
	DECLARE @messages TABLE
		(
			handle UNIQUEIDENTIFIER,
			messageType NVARCHAR(256)
		)   
	
	WHILE 1 = 1
	BEGIN
		BEGIN TRAN
			WAITFOR
			(RECEIVE TOP(1) conversation_handle, message_type_name
			FROM [Control]
			INTO @messages), 
			TIMEOUT 0;				
			IF @@ROWCOUNT = 0 
			BEGIN
				COMMIT TRAN
				GOTO Finish
			END	
			
			SELECT @messageType = messageType, @handle = handle FROM @messages
			IF(@handle IS NOT NULL)
			BEGIN		
				END CONVERSATION @handle;    						
			END
		COMMIT TRAN		
		
		IF(@messageType = 'http://schemas.codeflows.net/messages/control/retry') BEGIN
			EXEC FailedItemsReader
		END
	END	
	Finish:	
END
GO

CREATE PROCEDURE QueueControlCommand
	@command INT
AS
BEGIN
	BEGIN TRAN
		-- Enqueue retry message    
		DECLARE @controlDialogHandle UNIQUEIDENTIFIER								
					
		IF(@command = 1) BEGIN
			BEGIN DIALOG @controlDialogHandle
					FROM SERVICE
					[http://schemas.codeflows.net/services/client]
					TO SERVICE
					N'http://schemas.codeflows.net/services/control'
					ON CONTRACT
					[http://schemas.codeflows.net/contracts/control]
					WITH
						ENCRYPTION = OFF;
		
				SEND ON CONVERSATION @controlDialogHandle
						MESSAGE TYPE 
						[http://schemas.codeflows.net/messages/control/retry];	     		
							
			END CONVERSATION @controlDialogHandle						
		END
	COMMIT TRAN
END
GO
